package DBLayer;


import java.util.ArrayList;
import ModelLayer.*;

import java.sql.*;



/**
 * @author (Gruppe 5)
 * @version 04-04-2013
 */

public class ProductDB {
	private ArrayList<Product> pa;
	private ArrayList<Equipment> aeq;
	private ArrayList<Clothing> cloth;
	private ArrayList<GunReplicas> gp;
    private  Connection con;
    /** Creates a new instance of DBProduct */
    public ProductDB() {
    	 con = ConnectionDB.getInstance().getDBcon();
    	 pa = new ArrayList<Product>();
    	 aeq = new ArrayList<Equipment>();
    	 cloth = new ArrayList<Clothing>();
    	 gp = new ArrayList<GunReplicas>();
    	 
    }
    
    public void createProduct(Product p)
    {
    	String query = "insert into Product values ('"+p.getName()+"', '"+p.getPurchasePrice()+"', '"+p.getSalesPrice()+"', '"+p.getRentPrice()+"', '"+p.getCountryOfOrigin()+"', '"+p.getMinStock()+"', '"+p.getStock()+"','"+p.getProductType()+"', '"+p.getSupplierId()+"')";
    	
    	try
    	{
    		Statement stmt = con.createStatement();
    		stmt.setQueryTimeout(5);
    		stmt.executeUpdate(query);
    		
    		
    	}
    	catch (SQLException e)
    	{
    		System.out.println("fail!");
    	}
    }


    
    // build Product
    private Clothing buildClothing(ResultSet results)
    {   Clothing c = new Clothing();
        try{ // the columns from the table product  are used

              c.setName(results.getString("name"));
              c.setPurchasePrice(results.getDouble("PurchasePrice"));
              c.setSalesPrice(results.getDouble("SalesPrice"));
              c.setRentPrice(results.getDouble("RentPrice"));
              c.setCountryOfOrigin(results.getString("CountryOfOrigin"));
              c.setMinStock(results.getInt("minStock"));
              c.setStock(results.getInt("Stock"));
              c.setProductType(results.getString("productType"));
              c.setSize(results.getString("Size"));
              c.setColour(results.getString("Colour"));         
              c.setSupplierId(results.getInt("supplierid"));

              
        }
       catch(Exception e)
       {
           System.out.println("error in building the Product object");
       }
       return c;
    }

    // build Equipment
    private Equipment buildEquipment(ResultSet results)
    {   
        Equipment eq = new Equipment();
        try{ // the columns from the table product  are used
              eq.setName(results.getString("name"));
              eq.setPurchasePrice(results.getDouble("PurchasePrice"));
              eq.setSalesPrice(results.getDouble("SalesPrice"));
              eq.setRentPrice(results.getDouble("RentPrice"));
              eq.setCountryOfOrigin(results.getString("CountryOfOrigin"));
              eq.setMinStock(results.getInt("minStock"));
              eq.setStock(results.getInt("Stock"));
              eq.setProductType(results.getString("productType"));
              eq.setEquipment(results.getString("type"),results.getString("Description"));
              eq.setSupplierId(results.getInt("supplierId"));
              
        }
       catch(Exception e)
       {
           System.out.println("error in building the Equipment object");
       }
       return eq;
    }
    
    // build GunReplica
    private GunReplicas buildGunReplicas(ResultSet results)
    {   
        GunReplicas gun = new GunReplicas();
        try{ // the columns from the table product  are used
              gun.setName(results.getString("name"));
              gun.setPurchasePrice(results.getDouble("PurchasePrice"));
              gun.setSalesPrice(results.getDouble("SalesPrice"));
              gun.setRentPrice(results.getDouble("RentPrice"));
              gun.setCountryOfOrigin(results.getString("CountryOfOrigin"));
              gun.setMinStock(results.getInt("minStock"));
              gun.setStock(results.getInt("Stock"));
              gun.setProductType(results.getString("productType"));
              gun.setFabric(results.getString("Fabric"));
              gun.setCalibre(results.getString("Calibre"));
              gun.setSupplierId(results.getInt("supplierId"));
              
        }
       catch(Exception e)
       {
           System.out.println("error in building the Gun Replica object");
       }
       return gun;
    }
    
	public Product searchProductId(int id, boolean b) {
		// TODO Auto-generated method stub
		return null;
    }
	// finder et produkt ud fra produkt typen
	
	
  	public ArrayList<Clothing> findProductClothing(String productType)
    {
  		cloth.clear();
  		String query="SELECT name, PurchasePrice, SalesPrice, RentPrice, CountryOfOrigin, minStock, Stock, productType, supplierId, size, colour  FROM Product, Clothing WHERE producttype = 'Clothing' and id = productid";
        
    	Clothing c = null;
    	
    	try
		{
			// Udfør query på databasen.
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			ResultSet rs = stmt.executeQuery(query);
			
			// Tag første resultat ud af db.
			while (rs.next()) {
				
			c = buildClothing(rs);
			cloth.add(c);
			}
          rs.close();
		}
		catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return cloth;
    }
  	
	public ArrayList<Equipment> findProductEquipment(String productType)
    {
  		aeq.clear();
  		String query="SELECT name, PurchasePrice,SalesPrice,RentPrice,CountryOfOrigin,minStock,Stock, productType, type, description, supplierid  FROM Product, Equipment WHERE producttype = 'Equipment' and id = productid";
        
    	Equipment eq = null;
    	
    	try
		{
			// Udfør query på databasen.
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			ResultSet rs = stmt.executeQuery(query);
			
			// Tag første resultat ud af db.
			while (rs.next()) {
				
			eq = buildEquipment(rs);
			aeq.add(eq);
			}
          rs.close();
		}
		catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return aeq;
    }

	public ArrayList<GunReplicas> findProductGunReplicas(String productType)
    {
  		gp.clear();
  		String query="SELECT name, PurchasePrice, SalesPrice, RentPrice, CountryOfOrigin, minStock, Stock, productType, supplierId, fabric, calibre  FROM Product, GunReplicas WHERE producttype = 'Gun Replicas' and id = productid";
        
    	GunReplicas gun = null;
    	
    	try
		{
			// Udfør query på databasen.
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			ResultSet rs = stmt.executeQuery(query);
			
			// Tag første resultat ud af db.
			while (rs.next()) {
				
			gun = buildGunReplicas(rs);
			gp.add(gun);
			}
          rs.close();
		}
		catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return gp;
    }
}
    	

    
    

